﻿using System;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;

/// <summary>
/// 数据库扩展类
/// </summary>
public static class Class_DButil
    {
        /// <summary>
        /// 检测数据库连接是否超时
        /// </summary>
        /// <param name="conn">测试连接</param>
        /// <param name="timeout">限制超时秒数 默认5秒</param>
        public static bool TestOpen(this SqlConnection conn, int timeout = 5)
        {
            Stopwatch sw = new Stopwatch();
            bool connectSuccess = false;

            Thread t = new Thread(delegate ()
            {
                try
                {
                    sw.Start();
                    conn.Open();
                    connectSuccess = true;
                     

                }
                catch { }
            });

            t.IsBackground = true;
            t.Start();

            while (timeout > sw.ElapsedMilliseconds)
                if (t.Join(1))
                    break;

            return connectSuccess;
        }

    /// <summary>
    /// 简化数据库操作 失败返回空的数据表 通过检测datatable.rows.count=0判断
    /// </summary>
    /// <param name="Conn"></param>
    /// <param name="SQL"></param>
    /// <returns></returns>
    public static System.Data.DataTable GetDataTable(SqlConnection Conn, string SQL)
    {
        try
        {
            if (Conn.State != ConnectionState.Open) { Conn.Open(); }
            SqlCommand SQLcmd = new SqlCommand(SQL, Conn);
            SqlDataAdapter Da = new SqlDataAdapter(SQLcmd);
            System.Data.DataTable DT = new System.Data.DataTable();
            string TableName = Class_String.Mid(SQL, "from ", " ", false, false);
            DT.TableName = TableName;
            Da.Fill(DT);
            return DT;
        }
        catch (Exception)
        {
            return new System.Data.DataTable();
        }
    }
    /// <summary>
    /// 执行SQL语句，返回影响的行数 失败返回-2
    /// </summary>
    /// <param name="Conn"></param>
    /// <param name="SQL"></param>
    /// <returns></returns>
    public static Int32 SQLexec(SqlConnection Conn, string SQL)
    {
        try
        {
            SqlCommand SQLcmd = new SqlCommand(SQL, Conn);
            return SQLcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.StackTrace);
            return -2;
        }

    }

    /// <summary>
    /// 将二进制数据(文件)插入数据表或修改
    /// </summary>
    /// <param name="Conn">数据库连接</param>
    /// <param name="Table_name">数据表名</param>
    /// <param name="Field_Name">二进制字段名</param>
    /// <param name="ByteDATA">二进制数据</param>
    /// <param name="AddNew_Update">添加或者修改  默认添加true。如果修改false,必须提供Recordset_ID参数，并且数据表存在字段id</param>
    /// <param name="Recordset_ID">需要修改的字段id 值</param>
    /// <returns></returns>
    public static bool WriteDB_Binary(SqlConnection Conn,string Table_name,string Field_Name,byte[] ByteDATA,bool AddNew_Update=true,int Recordset_ID=0)
    {
        try
        {
            if (Recordset_ID < 1) { return false; }
            string SQL = string.Empty;
            if (AddNew_Update)
            {
                SQL = string.Format("Inser Into {0} ({1}) values (@bytedata)",Table_name,Field_Name);
            }else
            {
                SQL = string.Format("Update {0} set {1}=@bytedata where id={2}",Table_name,Field_Name,Recordset_ID);
            }
           
            SqlCommand cmd = new SqlCommand(SQL, Conn);
            cmd.Parameters.Add("@bytedata", SqlDbType.Binary, ByteDATA.Length);
            cmd.Parameters["@bytedata"].Value = ByteDATA;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            return true;
        }catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
        
    }

    /// <summary>
    /// 将文件上传到SQL数据库中的FileTable表中 需要SQL SERVER开启FileTable功能
    /// </summary>
    /// <param name="Conn">数据库连接</param>
    /// <param name="strFileTableName">文件数据表名</param>
    /// <param name="strFilePath">文件路径及文件名</param>
    /// <param name="CopyMov">复制或移动文件，默认复制</param>
    /// <returns></returns>
    public static bool File_2_FileTable(SqlConnection Conn,string strFileTableName,string strFilePath,bool CopyMove = true)
    {
        try
        {
            if (!File.Exists(strFilePath))
            {
                return false;
            }
            FileInfo file_info = new FileInfo(strFilePath);
            string strRootPath = Get_FileTable_RootPath(Conn, strFileTableName);
            if (File.Exists(Path.Combine(strRootPath, file_info.Name)))
            {
                return false;
            }
            if (CopyMove)
            {
                file_info.CopyTo(Path.Combine(strRootPath, file_info.Name));
            }
            else
            {
                file_info.MoveTo(Path.Combine(strRootPath, file_info.Name));
            }
            return true;


        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }
    /// <summary>
    /// 取数据库连接中的FileTable的路径名 失败返回空
    /// </summary>
    /// <param name="Conn"></param>
    /// <param name="strFileTableName"></param>
    /// <returns></returns>
   private static string Get_FileTable_RootPath(SqlConnection Conn,string strFileTableName)
    {
        string strRootPath = string.Empty;
        try
        {
            StringBuilder Sb = new StringBuilder();
            Sb.AppendFormat("select FileTableRootPath('{0}') as [path]", strFileTableName);
            SqlCommand sqlCmd = new SqlCommand(Sb.ToString(), Conn);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            DataTable dt = new DataTable();
            sqlDa.Fill(dt);
            strRootPath = dt.Rows[0][0].ToString();
            sqlDa.Dispose();
            sqlCmd.Dispose();
        }
        catch(Exception ex)
        {
            Console.Write(ex.Message);
        }
        return strRootPath;
    }

    #region 对象与字符串转换部分
    /// <summary>
    /// 将DataTable序列化为字符串
    /// </summary>
    /// <param name="DT"></param>
    /// <returns></returns>
    public static String DataTableToString(DataTable DT)
    {
        try
        {
            DT.TableName = "hzh"; //不设置出错！！！
            System.Xml.Serialization.XmlSerializer XMLser = new System.Xml.Serialization.XmlSerializer(typeof(DataTable));
            StringBuilder sb = new StringBuilder();
            System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(sb);
            XMLser.Serialize(writer, DT);
            writer.Close();
            return sb.ToString();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return "";
        }
    }
    /// <summary>
    /// 将字符串转换为datatable
    /// </summary>
    /// <param name="Str"></param>
    /// <returns></returns>
    public static DataTable StringToDataTable(string Str)
    {
        try
        {
            System.Xml.Serialization.XmlSerializer XMLser = new System.Xml.Serialization.XmlSerializer(typeof(DataTable));
            StringReader StringR = new StringReader(Str);
            DataTable DT = (DataTable)XMLser.Deserialize(StringR);
            return DT;
        }
        catch (Exception)
        {
            return new DataTable();
        }


    }

    #endregion

    #region 配置系统部分
    /// <summary>
    /// 检查INI配置表是否存在 
    /// </summary>
    /// <param name="Conn"></param>
    /// <returns></returns>
    public static bool INI_Exit(SqlConnection Conn)
    {
        try
        {
            string SQL = "select * from  sys.tables where   name = 'table_ini'";
            DataTable DT = GetDataTable(Conn, SQL);
            if (DT.Rows.Count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception)
        {
            return false;
        }
       
    }
    /// <summary>
    /// 创建INI配置表
    /// </summary>
    /// <param name="Conn"></param>
    public static void INI_Create(SqlConnection Conn)
    {
        try
        {
            string SQL = "CREATE TABLE [dbo].[Table_ini]([ini_section] [nvarchar](50) NOT NULL,[ini_key] [nvarchar](50) NOT NULL,[ini_value] [nvarchar](255) NULL)";
            SQLexec(Conn, SQL);
        }
        catch(Exception)
        {
            return;
        }
    }
    /// <summary>
    /// 保存配置值
    /// </summary>
    /// <param name="Section">配置节名(不允许含有单引号)</param>
    /// <param name="Key">配置键名(不允许含有单引号)</param>
    /// <param name="Value">配置值</param>
    /// <param name="Conn">数据库连接</param>
    /// <returns></returns>
    public static bool SetINI(string Section,string Key,string Value,SqlConnection Conn)
    {
        try
        {
            if (Section.IndexOf('\'') > -1) { return false; }
            if (Key.IndexOf('\'') > -1) { return false; }
            Value = Value.Replace("'", "scsoft_danyinhao"); //将单引号替换为特殊字符串
            string SQL = string.Format("select * from table_ini where ini_section='{0}' and ini_key='{1}'", Section, Key);
            DataTable DT = GetDataTable(Conn, SQL);
            if(DT.Rows.Count == 1)
            {
                SQL = string.Format("update table_ini set ini_value='{2}' where  ini_section='{0}' and ini_key='{1}'", Section, Key, Value);
            }else
            {
                SQL = string.Format("INSERT INTO table_ini (ini_section,ini_key,ini_value) VALUES ('{0}','{1}','{2}')", Section, Key, Value);
            }
            if(SQLexec(Conn, SQL) == 1)
            {
                return true;
            }else
            {
                return false;
            }

        }
        catch (Exception)
        {
            return false;
        }
    }
    /// <summary>
    /// 取配置值
    /// </summary>
    /// <param name="Section">配置组名(不允许含有单引号)</param>
    /// <param name="Key">配置键名(不允许含有单引号)</param>
    /// <param name="Conn">数据库连接</param>
    /// <returns></returns>
    public static string GetINI(string Section,string Key,SqlConnection Conn)
    {
        try
        {
            if (Section.IndexOf('\'') > -1) { return ""; }
            if (Key.IndexOf('\'') > -1) { return ""; }
            string SQL = string.Format("select * from table_ini where ini_section='{0}' and ini_key='{1}'", Section, Key);
            DataTable dt= GetDataTable(Conn, SQL);
            if(dt.Rows.Count != 1)
            {
                return "";
            }
            else
            {
                string Ret = dt.Rows[0]["ini_value"].ToString();
                Ret = Ret.Replace("scsoft_danyinhao", "'");
                return Ret;
            }
        }
        catch(Exception)
        {
            return "";
        }
    }
    #endregion

    

    #region 分类递归部分 根绝ID找父ID 或者子ID
    //
    // 数据表结构
    //  id       ID_Parent 
    //  本条id   父类ID


    /// <summary>
    /// 根据给定的分类ID，返回分类所属的子分类ID用逗号分割的格式化字符串
    /// </summary>
    /// <param name="Conn">数据库连接</param>
    /// <param name="setID">给定的ID</param>
    /// <param name="TableName">数据表名</param>
    /// <returns>返回子分类的ID 失败返回空</returns>
    public static string RetChildrenID(SqlConnection Conn, Int32 setID,string TableName)
    {
        try
        {
            string SQL = string.Format("WITH Table_A(id,idP) as (SELECT ID,ID_Parent FROM {1} where ID={0} UNION ALL  SELECT A.ID,ID_Parent FROM {1} A,Table_A b where  a.ID_Parent=b.id ) select id from Table_A", setID.ToString(),TableName);
            DataTable DataTable_i = GetDataTable(Conn, SQL);
            string RetSTR = "";
            for (int i = 0; i < DataTable_i.Rows.Count; i++)
            {
                if (RetSTR != "")
                {
                    RetSTR = RetSTR + "," + DataTable_i.Rows[i][0].ToString();
                }
                else
                {
                    RetSTR = DataTable_i.Rows[i][0].ToString();
                }
            }
            return RetSTR;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.StackTrace);
            return "";
        }


    }
    /// <summary>
    /// 根据设置的分类ID，返回分类上级所有上级分类ID 用逗号分割的格式化字符串
    /// </summary>
    /// <param name="Conn">数据库连接</param>
    /// <param name="setID">给定的分类ID</param>
    /// <param name="tableName">给定的数据表名</param>
    /// <returns>返回逗号分隔符的字符串 失败为空</returns>
    public static string RetParentID(SqlConnection Conn, Int32 setID,string tableName)
    {
        try
        {
            string SQL = string.Format("WITH Table_A(id,idP) as (SELECT ID,ID_Parent FROM {1} where ID={0} UNION ALL SELECT A.ID,ID_Parent FROM {1} A,Table_A b where  A.ID=b.idP ) select id from Table_A",setID.ToString(),tableName);
            System.Data.DataTable DataTable_i = GetDataTable(Conn, SQL);
            string RetSTR = "";
            for (int i = 0; i < DataTable_i.Rows.Count; i++)
            {
                if (RetSTR != "")
                {
                    RetSTR = RetSTR + "," + DataTable_i.Rows[i][0].ToString();
                }
                else
                {
                    RetSTR = DataTable_i.Rows[i][0].ToString();
                }
            }
            return RetSTR;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.StackTrace);
            return "";
        }
    }
    /// <summary>
    /// 返回节点的ID
    /// </summary>
    /// <param name="Conn"></param>
    /// <param name="DeviceID"></param>
    /// <param name="TrueText_FalseID"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public static string GetRoot(SqlConnection Conn, string DeviceID, bool TrueText_FalseID,string tableName)
    {
        try
        {
            string SQL = string.Format("WITH Table_A(id,idP) as (SELECT ID,ID_Parent FROM {1} where ID={0} UNION ALL SELECT A.ID,ID_Parent FROM {1} A,Table_A b where  A.ID=b.idP ) select min(id) from Table_A", DeviceID.ToString(),tableName);
            System.Data.DataTable DataTable_i = GetDataTable(Conn, SQL);
            return DataTable_i.Rows[0][0].ToString(); 
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.StackTrace);
            return "";
        }
    }

    #endregion


  

}

/// <summary>
/// 类的反射
/// </summary>
public class class_Get_className
{
    /// <summary>
    /// 取类名
    /// </summary>
    /// <param name="class_index">所在层的类 0为本类名 1为上级类名  2为上上级类名</param>
    /// <returns></returns>
    public static string GetClassName(int class_index)
    {
        try
        {
            System.Diagnostics.StackTrace trace = new System.Diagnostics.StackTrace();
            System.Diagnostics.StackFrame frame = trace.GetFrame(1); //1代表上级 2代表上上级
            System.Reflection.MethodBase method = frame.GetMethod();
            return method.ReflectedType.Name;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return string.Empty;
        }
    }
    /// <summary>
    /// 返回方法名
    /// </summary>
    /// <param name="class_index">设置类的级别</param>
    /// <returns></returns>
    public static string GetMethodName(int class_index)
    {
        try
        {
            System.Diagnostics.StackTrace trace = new System.Diagnostics.StackTrace();
            System.Diagnostics.StackFrame frame = trace.GetFrame(class_index); //1代表上级 2代表上上级
            System.Reflection.MethodBase method = frame.GetMethod();
            return method.Name;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return string.Empty;
        }
    }

}
